﻿Imports System.Data.SqlClient
Imports UTNComputers.clsCatalogo
Imports UTNComputers.daoCatalogo
Public Class daoOrden_Servicio_Consumos
    'Funcion para el alta de Consumo de Orden de Servicio'
    Public Function Alta(ByVal OS_Consumo As clsOrden_Servicio_Consumos) As Boolean
        Dim Cn As New SqlConnection
        Dim Cm As New SqlCommand

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_ALTA_OS_CONSUMOS"

            Cm.Parameters.Add("@oscons_id", SqlDbType.Int)
            Cm.Parameters.Add("@osdet_id", SqlDbType.Int)
            Cm.Parameters.Add("@prod_id", SqlDbType.Int)
            Cm.Parameters.Add("@nro_serie", SqlDbType.VarChar)
            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)

            Cm.Parameters("@oscons_id").Value = OS_Consumo.Oscons_Id
            Cm.Parameters("@osdet_id").Value = OS_Consumo.Osdet_Id
            Cm.Parameters("@prod_id").Value = OS_Consumo.Prod_Id
            Cm.Parameters("@nro_serie").Value = OS_Consumo.Nro_Serie
            Cm.Parameters("@Cod_Suc").Value = OS_Consumo.Cod_Suc

            Cm.Connection = Cn

            Cm.ExecuteNonQuery()
            Cn.Close()
            Return True

        Catch ex As Exception
            Return False
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    'Funcion para la modificacion consumo de Orden de Servicio'
    Public Function Modificar(ByVal OS_Consumo As clsOrden_Servicio_Consumos) As Boolean
        Dim Cn As New SqlConnection
        Dim Cm As New SqlCommand

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_MODI_OS_CONSUMOS"

            Cm.Parameters.Add("@oscons_id", SqlDbType.Int)
            Cm.Parameters.Add("@osdet_id", SqlDbType.Int)
            Cm.Parameters.Add("@prod_id", SqlDbType.Int)
            Cm.Parameters.Add("@nro_serie", SqlDbType.VarChar)
            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)

            Cm.Parameters("@oscons_id").Value = OS_Consumo.Oscons_Id
            Cm.Parameters("@osdet_id").Value = OS_Consumo.Osdet_Id
            Cm.Parameters("@prod_id").Value = OS_Consumo.Prod_Id
            Cm.Parameters("@nro_serie").Value = OS_Consumo.Nro_Serie
            Cm.Parameters("@Cod_Suc").Value = OS_Consumo.Cod_Suc

            Cm.Connection = Cn

            Cm.ExecuteNonQuery()
            Cn.Close()
            Return True

        Catch ex As Exception
            Return False
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    'Funcion para Traer un Consumo de Orden de Servicio por ID'
    Public Function TraeConsumoOrdenServicio(ByVal oscons_id As Integer) As clsOrden_Servicio_Consumos
        Dim Cn As New SqlClient.SqlConnection
        Dim Cm As New SqlClient.SqlCommand
        Dim Dr As SqlClient.SqlDataReader
        Dim OS_Consumo As New clsOrden_Servicio_Consumos()

        Dim Conf As New clsConfiguraciones()
        Dim dao_Conf As New daoConfiguraciones()
        Conf = dao_Conf.TraeConfiguraciones()

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_TRAER_OS_COMSUMO"

            Cm.Parameters.Add("@oscons_id", SqlDbType.Int)
            Cm.Parameters("@oscons_id").Value = oscons_id
            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)
            Cm.Parameters("@Cod_Suc").Value = Conf.Cod_Suc

            Cm.Connection = Cn
            Dr = Cm.ExecuteReader
            If Dr.HasRows = True Then
                While Dr.Read
                    OS_Consumo.Oscons_Id = Dr.GetInt32(0)
                    OS_Consumo.Osdet_Id = Dr.GetInt32(1)
                    OS_Consumo.Prod_Id = Dr.GetInt32(2)
                    If Dr.IsDBNull(3) Then
                        OS_Consumo.Nro_Serie = "-"
                    Else
                        OS_Consumo.Nro_Serie = Dr.GetString(3).Trim.ToUpper
                    End If
                    OS_Consumo.Cod_Suc = Dr.GetInt32(4)

                    Dim CatalogoDao As New daoCatalogo()
                    OS_Consumo.Producto = CatalogoDao.TraeArticulo(OS_Consumo.Prod_Id)
                    CatalogoDao.Dispose()
                End While

                Dr.Close()
                Cn.Close()
            End If
            Return OS_Consumo
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    'Funcion para Traer Todos los consumos de una detalle de orden de servicio por '
    Public Function TraeConsumosOrdenServicio(ByVal osdet_id As Integer) As List(Of clsOrden_Servicio_Consumos)
        Dim Cn As New SqlClient.SqlConnection
        Dim Cm As New SqlClient.SqlCommand
        Dim Dr As SqlClient.SqlDataReader
        Dim ListaConsumos_OS As New List(Of clsOrden_Servicio_Consumos)

        Dim Conf As New clsConfiguraciones()
        Dim dao_Conf As New daoConfiguraciones()
        Conf = dao_Conf.TraeConfiguraciones()

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_TRAER_OS_COMSUMO_X_OSDET_ID"

            Cm.Parameters.Add("@osdet_id", SqlDbType.Int)
            Cm.Parameters("@osdet_id").Value = osdet_id

            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)
            Cm.Parameters("@Cod_Suc").Value = Conf.Cod_Suc

            Cm.Connection = Cn
            Dr = Cm.ExecuteReader
            If Dr.HasRows = True Then
                While Dr.Read
                    Dim OS_Consumo As New clsOrden_Servicio_Consumos()
                    OS_Consumo.Oscons_Id = Dr.GetInt32(0)
                    OS_Consumo.Osdet_Id = Dr.GetInt32(1)
                    OS_Consumo.Prod_Id = Dr.GetInt32(2)
                    If Dr.IsDBNull(3) Then
                        OS_Consumo.Nro_Serie = "-"
                    Else
                        OS_Consumo.Nro_Serie = Dr.GetString(3).Trim.ToUpper
                    End If
                    OS_Consumo.Cod_Suc = Dr.GetInt32(4)

                    Dim CatalogoDao As New daoCatalogo()
                    OS_Consumo.Producto = CatalogoDao.TraeArticulo(OS_Consumo.Prod_Id)
                    CatalogoDao.Dispose()

                    ListaConsumos_OS.Add(OS_Consumo)
                    OS_Consumo.Dispose()
                End While

                Dr.Close()
                Cn.Close()
            End If
            Return ListaConsumos_OS
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    '---------------------------------------------------------------------
    ' Constructor de la Clase
    '---------------------------------------------------------------------
    Public Sub New()

        MyBase.New()

    End Sub

    '---------------------------------------------------------------------
    ' Destructor de la Clase
    '---------------------------------------------------------------------
    Public Sub Dispose()

        MyBase.Finalize()

    End Sub

End Class
